/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dbhandler;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.lang.System;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Pattern;
import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;

public class DBConnector {

    protected int port;
    protected String user;
    protected String dbname;
    protected String server;
    protected String password;
    protected String connString;
    protected Connection connection;

    public Connection getConnection() {
        return connection;
    }

    public DBConnector(String server, int port, String user, String password, String dbname) {
        this.server = server;
        this.port = port;
        this.user = user;
        this.password = password;
        this.dbname = dbname;

    }

    public void connectToDB() throws ClassNotFoundException, SQLException {
        //Instantiate MySQL driver and create connection
        Class.forName("com.mysql.jdbc.Driver");
        connString = "jdbc:mysql://"
                + this.server
                + ":"
                + this.port
                + "/"
                + this.dbname
                + "?user="
                + this.user
                + "&password="
                + this.password;
        connection = DriverManager.getConnection(connString);
    }

    public void disconnectDB() throws SQLException {
        this.connection.close();
    }

    public boolean checkusername(String username) throws SQLException, ClassNotFoundException {
        boolean check = true;
        CallableStatement callable = null;
        ResultSet result = null;
        String authCall = "{ call select_username(?) }";
        //Connect to the DB
        this.connectToDB();

        callable = this.connection.prepareCall(authCall);
        callable.setString(1, username);
        result = callable.executeQuery();
        while (result.next()) {

            if (result.getString("username").equals(username)) {
                check = false;
            }
        }
        //Close DB connection
        this.disconnectDB();
        //debugging println
        //System.out.println("check:  " + check);
        return check;
    }

    public boolean checktitle(String title, String mtype) throws SQLException, ClassNotFoundException {
        boolean check = false;
        CallableStatement callable = null;
        ResultSet result = null;
        String authCall = "{ call select_title(?,?) }";
        //Connect to the DB
        this.connectToDB();

        callable = this.connection.prepareCall(authCall);
        callable.setString(1, title);
        callable.setString(2, mtype);
        result = callable.executeQuery();
        while (result.next()) {

            if (result.getString("title").equals(title)) {
                check = true;
            }
        }
        //Close DB connection
        this.disconnectDB();
        //debugging println
        //System.out.println("check:  " + check);
        return check;
    }

    public boolean deletetitle(String title, String category, String mtype) throws SQLException, ClassNotFoundException {
        boolean check = true;
        CallableStatement callable = null;
        ResultSet result = null;
        String authCall = "{ call delete_title(?,?,?) }";
        //Connect to the DB
        this.connectToDB();

        callable = this.connection.prepareCall(authCall);
        callable.setString(1, title);
        callable.setString(2, category);
        callable.setString(3, mtype);
        result = callable.executeQuery();

        //Close DB connection
        this.disconnectDB();
        //debugging println
        //System.out.println("check:  " + check);
        return check;
    }

    public boolean checkstatus(String username) throws SQLException, ClassNotFoundException {
        System.out.println("wrror me at checkunlocker before status");
        boolean check = false;
        CallableStatement callable = null;
        ResultSet result = null;
        String authCall = "{ call select_status(?) }";
        //Connect to the DB
        this.connectToDB();

        callable = this.connection.prepareCall(authCall);
        callable.setString(1, username);
        result = callable.executeQuery();
        while (result.next()) {

            if (result.getInt("status") == 1) {
                check = true;
            }
        }
        //Close DB connection
        this.disconnectDB();
        //debugging println
        //System.out.println("check:  " + check);
        System.out.println("wrror me at checkunlocker after status");
        return check;
    }

    public boolean check_creditinfo(String username) throws SQLException, ClassNotFoundException {
        boolean check = false;
        CallableStatement callable = null;
        ResultSet result = null;
        String authCall = "{ call select_creditinfo(?) }";
        //Connect to the DB
        this.connectToDB();

        callable = this.connection.prepareCall(authCall);
        callable.setString(1, username);
        result = callable.executeQuery();
        while (result.next()) {

            if (result.getString("username").equals(username)) {
                check = true;
            }
        }
        //Close DB connection
        this.disconnectDB();
        return check;
    }

    public boolean checkemail(String email) throws SQLException, ClassNotFoundException {
        boolean check = true;
        CallableStatement callable = null;
        ResultSet result = null;
        String authCall = "{ call select_email(?) }";
        //Connect to the DB
        this.connectToDB();

        callable = this.connection.prepareCall(authCall);
        callable.setString(1, email);
        result = callable.executeQuery();
        while (result.next()) {

            if (result.getString("email").equals(email)) {
                check = false;
            }
        }
        //Close DB connection
        this.disconnectDB();
        //debugging println
        //System.out.println("check:  " + check);
        return check;
    }

    public boolean checkcountry(String country) throws SQLException, ClassNotFoundException {
        boolean check = false;
        CallableStatement callable = null;
        ResultSet result = null;
        String authCall = "{ call select_country(?) }";
        //Connect to the DB
        this.connectToDB();

        callable = this.connection.prepareCall(authCall);
        callable.setString(1, country);
        result = callable.executeQuery();
        while (result.next()) {

            if (result.getString("countryname").equals(country)) {
                check = true;
            }
        }
        //Close DB connection
        this.disconnectDB();
        //debugging println
        //System.out.println("check:  " + check);
        return check;
    }

    public static boolean isDate(CharSequence date) {

        // some regular expression
        String time = "(\\s(([01]?\\d)|(2[0123]))[:](([012345]\\d)|(60))"
                + "[:](([012345]\\d)|(60)))?"; // with a space before, zero or one time

        // no check for leap years (Schaltjahr)
        // and 31.02.2006 will also be correct
        String day = "(([12]\\d)|(3[01])|(0?[1-9]))"; // 01 up to 31
        String month = "((1[012])|(0\\d))"; // 01 up to 12
        String year = "\\d{4}";

        // define here all date format
        ArrayList<Pattern> patterns = new ArrayList<Pattern>();
        patterns.add(Pattern.compile(year + "[-.]" + month + "[-.]" + day + time));
        patterns.add(Pattern.compile(year + "-" + month + "-" + day + time));

        // here you can add more date formats if you want

        // check dates
        for (Pattern p : patterns) {
            if (p.matcher(date).matches()) {
                return true;
            }
        }

        return false;

    }

    public boolean checkusernamelogin(String username) throws SQLException, ClassNotFoundException {
        boolean check = true;
        CallableStatement callable = null;
        ResultSet result = null;
        String authCall = "{ call select_username(?) }";
        String authCall3 = "{ call login_countf(?) }";
        //Connect to the DB
        this.connectToDB();

        callable = this.connection.prepareCall(authCall);
        callable.setString(1, username);
        result = callable.executeQuery();
        while (result.next()) {

            if (result.getString("username").equals(username)) {
                callable = this.connection.prepareCall(authCall3);
                callable.setString(1, username);
                callable.executeQuery();

                check = false;
            }
        }
        //Close DB connection
        this.disconnectDB();
        //debugging println
        //System.out.println("check:  " + check);
        return check;
    }

    public boolean checkllogin(String username, String password) throws SQLException, ClassNotFoundException {
        boolean success = false;
        //System.out.println("444444");
        //salt password, select salt compare to hash
        CallableStatement callable = null;
        ResultSet result = null;
        String authCall = "{ call select_login(?,?) }";
        String authCall2 = "{ call login_count(?) }";
        //Connect to the DB
        this.connectToDB();

        callable = this.connection.prepareCall(authCall);
        callable.setString(1, username);
        callable.setString(2, password);
        result = callable.executeQuery();
        //System.out.println(result + "result");

        while (result.next()) {
            if (result.getString("username").equals(username)) {
                success = true;

                callable = this.connection.prepareCall(authCall2);
                callable.setString(1, username);
                callable.executeQuery();
            }
        }
        //Close DB connection
        this.disconnectDB();
        //debugging println

        return success;

    }

    public static String byteToBase64(byte[] data) {
        BASE64Encoder endecoder = new BASE64Encoder();
        return endecoder.encode(data);
    }

    /**
     * From a base 64 representation, returns the corresponding byte[]
     *
     * @param data String The base64 representation
     * @return byte[]
     * @throws IOException
     */
    public static byte[] base64ToByte(String data) throws IOException {
        BASE64Decoder decoder = new BASE64Decoder();
        return decoder.decodeBuffer(data);
    }

    public byte[] getHash(String password, byte[] salt) throws NoSuchAlgorithmException, UnsupportedEncodingException {
        MessageDigest digest = MessageDigest.getInstance("SHA-256");
        digest.reset();
        digest.update(salt);
        byte[] input = digest.digest(password.getBytes("UTF-8"));

        return input;
    }

    public Integer getaccounttype(String username, String password) throws ClassNotFoundException, SQLException {

        Integer rac;
        rac = 0;
        CallableStatement callable = null;
        ResultSet result = null;
        String authCall = "{ call select_accounttype(?,?) }";
        //Connect to the DB
        this.connectToDB();

        callable = this.connection.prepareCall(authCall);
        callable.setString(1, username);
        callable.setString(2, password);
        result = callable.executeQuery();

        while (result.next()) {
            if (result.getString("username").equals(username)) {

                rac = result.getInt("accounttype");
            }
        }
        //Close DB connection
        this.disconnectDB();
        //debugging println




        return rac;
    }

    public String getfname(String username, String password) throws ClassNotFoundException, SQLException {

        String fname = null;

        CallableStatement callable = null;
        ResultSet result = null;
        String authCall = "{ call select_fname(?,?) }";
        //Connect to the DB
        this.connectToDB();

        callable = this.connection.prepareCall(authCall);
        callable.setString(1, username);
        callable.setString(2, password);
        result = callable.executeQuery();

        while (result.next()) {
            if (result.getString("username").equals(username)) {

                fname = result.getString("fname");
            }
        }
        //Close DB connection
        this.disconnectDB();
        //debugging println

        return fname;
    }

    public void changePassword(String userName, String password, String hashpassword) throws ClassNotFoundException, SQLException {
        CallableStatement callable = null;
        String createCall = "{ call change_password(?,?,?) }";
        this.connectToDB();
        callable = this.connection.prepareCall(createCall);
        callable.setString(1, userName);
        callable.setString(2, password);
        callable.setString(3, hashpassword);
        callable.executeQuery();
        this.disconnectDB();
    }

    public void createUser(String userName, String password1, String hash, String salt,
            String firstName, String lastName, String middleInitial,
            String email, int type) throws ClassNotFoundException, SQLException, NoSuchAlgorithmException {


        CallableStatement callable = null;
        String createCall = "{ call insert_customer_details(?,?,?,?,?,?,?,?,?) }";
        //Connect to DB
        this.connectToDB();

        callable = this.connection.prepareCall(createCall);
        callable.setString(1, userName);
        callable.setString(2, password1);
        callable.setString(3, hash);
        callable.setString(4, salt);
        callable.setString(5, firstName);
        callable.setString(6, lastName);
        callable.setString(7, middleInitial);
        callable.setString(8, email);
        callable.setInt(9, type);
        callable.executeQuery();
        this.disconnectDB();

    }

    public void createProduct(String title, String addinfo, String category, String type,
            String sypnosis, String price) throws ClassNotFoundException, SQLException, NoSuchAlgorithmException {


        CallableStatement callable = null;
        String createCall = "{ call insert_product_details(?,?,?,?,?,?) }";
        //Connect to DB
        this.connectToDB();

        callable = this.connection.prepareCall(createCall);
        callable.setString(1, title);
        callable.setString(2, addinfo);
        callable.setString(3, category);
        callable.setString(4, type);
        callable.setString(5, sypnosis);
        callable.setString(6, price);
        callable.executeQuery();
        this.disconnectDB();

    }

    public void setCreditInfo(String dhousenumber, String dstreet, String dsubdivision, String dcity,
            String dpostal, String dcountry, String bhousenumber, String bstreet, String bsubdivision, String bcity,
            String bpostal, String bcountry, String firstName, String lastName, String middleInitial,
            String creditcardnumber, String creditcardtype, String creditcardexpiration,
            String cusername, int caccounttype) throws ClassNotFoundException, SQLException, NoSuchAlgorithmException {


        CallableStatement callable = null;
        String createCall = "{ call insert_credit_info(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }"; //Connect to DB
        this.connectToDB();

        callable = this.connection.prepareCall(createCall);
        callable.setString(1, dhousenumber);
        callable.setString(2, dstreet);
        callable.setString(3, dsubdivision);
        callable.setString(4, dcity);
        callable.setString(5, dpostal);
        callable.setString(6, dcountry);
        callable.setString(7, bhousenumber);
        callable.setString(8, bstreet);
        callable.setString(9, bsubdivision);
        callable.setString(10, bcity);
        callable.setString(11, bpostal);
        callable.setString(12, bcountry);
        callable.setString(13, firstName);
        callable.setString(14, lastName);
        callable.setString(15, middleInitial);
        callable.setString(16, creditcardnumber);
        callable.setString(17, creditcardtype);
        callable.setString(18, creditcardexpiration);
        callable.setString(19, cusername);
        callable.setInt(20, caccounttype);
        callable.executeQuery();
        this.disconnectDB();

    }

    public void updateProduct(String title, String addinfo, String category, String type,
            String sypnosis, String price) throws ClassNotFoundException, SQLException, NoSuchAlgorithmException {


        CallableStatement callable = null;
        String createCall = "{ call update_product_details(?,?,?,?,?,?) }";
        //Connect to DB
        this.connectToDB();

        callable = this.connection.prepareCall(createCall);
        callable.setString(1, title);
        callable.setString(2, addinfo);
        callable.setString(3, category);
        callable.setString(4, type);
        callable.setString(5, sypnosis);
        callable.setString(6, price);
        callable.executeQuery();
        this.disconnectDB();

    }

    public void updateCreditInfo(String dhousenumber, String dstreet, String dsubdivision, String dcity,
            String dpostal, String dcountry, String bhousenumber, String bstreet, String bsubdivision, String bcity,
            String bpostal, String bcountry, String firstName, String lastName, String middleInitial,
            String creditcardnumber, String creditcardtype, String creditcardexpiration,
            String cusername, int caccounttype) throws ClassNotFoundException, SQLException, NoSuchAlgorithmException {


        CallableStatement callable = null;
        String createCall = "{ call update_credit_details(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";
        //Connect to DB
        this.connectToDB();

        callable = this.connection.prepareCall(createCall);
        callable.setString(1, dhousenumber);
        callable.setString(2, dstreet);
        callable.setString(3, dsubdivision);
        callable.setString(4, dcity);
        callable.setString(5, dpostal);
        callable.setString(6, dcountry);
        callable.setString(7, bhousenumber);
        callable.setString(8, bstreet);
        callable.setString(9, bsubdivision);
        callable.setString(10, bcity);
        callable.setString(11, bpostal);
        callable.setString(12, bcountry);
        callable.setString(13, firstName);
        callable.setString(14, lastName);
        callable.setString(15, middleInitial);
        callable.setString(16, creditcardnumber);
        callable.setString(17, creditcardtype);
        callable.setString(18, creditcardexpiration);
        callable.setString(19, cusername);
        callable.setInt(20, caccounttype);
        callable.executeQuery();
        this.disconnectDB();

    }

    public void updatecount(String userName) {
        throw new UnsupportedOperationException("Not yet implemented");
    }
}
